-- 性能优化索引脚本
-- 基于实际查询场景优化数据库性能

-- 用户表性能索引
-- 用户名查询（登录场景）
CREATE INDEX IF NOT EXISTS idx_sys_user_username_status ON kx_sys_user(username, status) WHERE status = 1;

-- 部门用户查询（数据权限场景）
CREATE INDEX IF NOT EXISTS idx_sys_user_dept_status ON kx_sys_user(dept_id, status) WHERE status = 1;

-- 用户状态和创建时间复合索引（列表查询）
CREATE INDEX IF NOT EXISTS idx_sys_user_status_create_time ON kx_sys_user(status, create_time DESC);

-- 邮箱和手机号查询索引
CREATE INDEX IF NOT EXISTS idx_sys_user_email ON kx_sys_user(email) WHERE email IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_sys_user_phone ON kx_sys_user(phone) WHERE phone IS NOT NULL;

-- 角色表性能索引
-- 角色状态和排序复合索引
CREATE INDEX IF NOT EXISTS idx_sys_role_status_sort ON kx_sys_role(status, role_sort) WHERE status = 1;

-- 角色权限字符查询
CREATE INDEX IF NOT EXISTS idx_sys_role_key_status ON kx_sys_role(role_key, status) WHERE status = 1;

-- 菜单表性能索引
-- 父级菜单查询（树形结构）
CREATE INDEX IF NOT EXISTS idx_sys_menu_parent_sort ON kx_sys_menu(parent_id, order_num) WHERE status = 1;

-- 菜单类型和状态复合索引
CREATE INDEX IF NOT EXISTS idx_sys_menu_type_status ON kx_sys_menu(menu_type, status, visible);

-- 权限字符查询索引
CREATE INDEX IF NOT EXISTS idx_sys_menu_perms ON kx_sys_menu(perms) WHERE perms IS NOT NULL AND status = 1;

-- 部门表性能索引
-- 父级部门查询（树形结构）
CREATE INDEX IF NOT EXISTS idx_sys_dept_parent_sort ON kx_sys_dept(parent_id, order_num) WHERE status = 1;

-- 祖先路径查询索引（层级权限）
CREATE INDEX IF NOT EXISTS idx_sys_dept_ancestors ON kx_sys_dept USING GIN(string_to_array(ancestors, ','));

-- 部门状态索引
CREATE INDEX IF NOT EXISTS idx_sys_dept_status ON kx_sys_dept(status);

-- 用户角色关联表索引
-- 用户查询角色
CREATE INDEX IF NOT EXISTS idx_sys_user_role_user_id ON kx_sys_user_role(user_id);

-- 角色查询用户
CREATE INDEX IF NOT EXISTS idx_sys_user_role_role_id ON kx_sys_user_role(role_id);

-- 角色菜单关联表索引
-- 角色查询菜单权限
CREATE INDEX IF NOT EXISTS idx_sys_role_menu_role_id ON kx_sys_role_menu(role_id);

-- 菜单查询关联角色
CREATE INDEX IF NOT EXISTS idx_sys_role_menu_menu_id ON kx_sys_role_menu(menu_id);

-- 操作日志表性能索引
-- 操作时间范围查询（最常用）
CREATE INDEX IF NOT EXISTS idx_log_operation_time_desc ON kx_log_operation(oper_time DESC);

-- 操作人员查询
CREATE INDEX IF NOT EXISTS idx_log_operation_name_time ON kx_log_operation(oper_name, oper_time DESC);

-- 业务类型查询
CREATE INDEX IF NOT EXISTS idx_log_operation_type_time ON kx_log_operation(business_type, oper_time DESC);

-- 操作状态查询
CREATE INDEX IF NOT EXISTS idx_log_operation_status_time ON kx_log_operation(status, oper_time DESC);

-- IP地址查询（安全审计）
CREATE INDEX IF NOT EXISTS idx_log_operation_ip_time ON kx_log_operation(oper_ip, oper_time DESC);

-- 登录日志表性能索引
-- 登录时间范围查询
CREATE INDEX IF NOT EXISTS idx_log_login_time_desc ON kx_log_login(login_time DESC);

-- 登录用户查询
CREATE INDEX IF NOT EXISTS idx_log_login_name_time ON kx_log_login(login_name, login_time DESC);

-- 登录状态查询
CREATE INDEX IF NOT EXISTS idx_log_login_status_time ON kx_log_login(status, login_time DESC);

-- IP地址查询（安全分析）
CREATE INDEX IF NOT EXISTS idx_log_login_ip_time ON kx_log_login(ipaddr, login_time DESC);

-- 字典表性能索引（如果存在）
-- 字典类型查询
CREATE INDEX IF NOT EXISTS idx_dict_type_status ON kx_dict_type(status) WHERE status = 1;

-- 字典数据类型查询
CREATE INDEX IF NOT EXISTS idx_dict_data_type_sort ON kx_dict_data(dict_type, dict_sort) WHERE status = 1;

-- 字典数据值查询
CREATE INDEX IF NOT EXISTS idx_dict_data_type_value ON kx_dict_data(dict_type, dict_value) WHERE status = 1;

-- 系统配置表性能索引（如果存在）
-- 配置键查询
CREATE INDEX IF NOT EXISTS idx_config_key ON kx_sys_config(config_key);

-- 配置类型查询
CREATE INDEX IF NOT EXISTS idx_config_type ON kx_sys_config(config_type) WHERE config_type IS NOT NULL;

-- 岗位表性能索引（如果存在）
-- 岗位状态和排序
CREATE INDEX IF NOT EXISTS idx_post_status_sort ON kx_sys_post(status, post_sort) WHERE status = 1;

-- 岗位编码查询
CREATE INDEX IF NOT EXISTS idx_post_code ON kx_sys_post(post_code);

-- 分区表建议（对于大数据量的日志表）
-- 按月分区操作日志表（需要手动执行，仅作为参考）
/*
-- 创建分区表的示例（PostgreSQL 10+）
-- 注意：这需要重建表，生产环境请谨慎操作

-- 1. 创建分区主表
CREATE TABLE kx_log_operation_partitioned (
    LIKE kx_log_operation INCLUDING ALL
) PARTITION BY RANGE (oper_time);

-- 2. 创建月份分区
CREATE TABLE kx_log_operation_y2024m01 PARTITION OF kx_log_operation_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE kx_log_operation_y2024m02 PARTITION OF kx_log_operation_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 继续创建其他月份分区...

-- 3. 数据迁移和表重命名（需要停机维护）
-- INSERT INTO kx_log_operation_partitioned SELECT * FROM kx_log_operation;
-- DROP TABLE kx_log_operation;
-- ALTER TABLE kx_log_operation_partitioned RENAME TO kx_log_operation;
*/

-- 创建统计信息更新函数（可选）
CREATE OR REPLACE FUNCTION update_table_statistics()
RETURNS void AS $$
BEGIN
    -- 更新主要表的统计信息
    ANALYZE kx_sys_user;
    ANALYZE kx_sys_role;
    ANALYZE kx_sys_menu;
    ANALYZE kx_sys_dept;
    ANALYZE kx_sys_user_role;
    ANALYZE kx_sys_role_menu;
    ANALYZE kx_log_operation;
    ANALYZE kx_log_login;
    
    RAISE NOTICE '表统计信息更新完成';
END;
$$ LANGUAGE plpgsql;

-- 注释：建议定期执行统计信息更新
-- SELECT update_table_statistics();

-- 创建索引使用情况监控视图
CREATE OR REPLACE VIEW v_index_usage AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan,
    CASE 
        WHEN idx_scan = 0 THEN '未使用'
        WHEN idx_scan < 100 THEN '使用较少'
        WHEN idx_scan < 1000 THEN '使用一般'
        ELSE '使用频繁'
    END as usage_level
FROM pg_stat_user_indexes 
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- 创建表大小监控视图
CREATE OR REPLACE VIEW v_table_sizes AS
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 提交注释
COMMENT ON INDEX idx_sys_user_username_status IS '用户登录查询优化索引';
COMMENT ON INDEX idx_sys_user_dept_status IS '部门用户数据权限查询索引';
COMMENT ON INDEX idx_log_operation_time_desc IS '操作日志时间倒序查询索引';
COMMENT ON INDEX idx_log_login_time_desc IS '登录日志时间倒序查询索引';
COMMENT ON VIEW v_index_usage IS '索引使用情况监控视图';
COMMENT ON VIEW v_table_sizes IS '表空间大小监控视图';
